package yh.core.funcs.workflow.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import yh.core.data.YHRequestDbConn;
import yh.core.global.YHBeanKeys;
import yh.core.util.db.YHDBUtility;

public class YHFlowDealResultApi {
	public static Map map=new HashMap();
	static{
		map.put("dwmc", "单位名称");/*查询字段进行映射，可以自行扩展到配置文件*/
	}
	/*
	 * 输入参数：工作流ID，工作流环节，时间，form_data title,
    * 
      *   按title进行groupby分组
      * 
	 * 
	 */
	public String getJsonByParameter(int flowTypeID,int flowPrcs,String beginDate,String endDate,String dataTitle, Connection dbConn ) throws ParseException{
		 Statement stmt = null;
		   ResultSet rs = null;// 结果集
		   String query1="select FORM_SEQ_ID FROM OA_FL_TYPE WHERE SEQ_ID="+flowTypeID;
           int formSeqId=0;
           String formDataTableName=null;
		   try {
		     stmt = dbConn.createStatement();
		     rs = stmt.executeQuery(query1);
		     if(rs.next()){
		    	 formSeqId = rs.getInt("FORM_SEQ_ID");
		    	 formDataTableName="FORM_DATA_"+flowTypeID+"_"+formSeqId;
		     }
		     
		   }catch(Exception ex){
		     ex.printStackTrace();
		   }finally {
		     YHDBUtility.close(stmt, rs, null);
		   }
		   String fieldName=getFieldByMgrKey(formSeqId,dataTitle,dbConn);
		   Timestamp startTime =  null;
	       Timestamp endTime =  null;
	       String query2="select a.run_id,a.begin_time,end_time,time_out_flag,"+fieldName+" from "+formDataTableName+" a inner join oa_fl_run b on a.RUN_ID=b.RUN_ID";
	       if(!"".equals(beginDate)&& beginDate!=null){
	    	   query2 += " and b.BEGIN_TIME >=? ";
	           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	           Date date  = sdf.parse(beginDate + " 00:00:00");
	           startTime = new  Timestamp(date.getTime());
	         }
	         if(!"".equals(endDate)&& endDate!=null){
	           query2 += " and b.BEGIN_TIME <=? ";
	           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	           Date date  = sdf.parse(endDate + " 23:59:59");
	           endTime = new  Timestamp(date.getTime());
	         }
	         query2+=" left join oa_fl_run_prcs c on a.RUN_ID=c.RUN_ID and c.PRCS_ID="+flowPrcs;
	         Map map=this.getRunIdSetByQuery(startTime, endTime, fieldName, dbConn, query2);
	         /**
	          * 

    { "people": [

    { "firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa" },

    { "firstName": "Jason", "lastName":"Hunter", "email": "bbbb"},

    { "firstName": "Elliotte", "lastName":"Harold", "email": "cccc" }

    ]}

	          */
	         StringBuffer sb=new StringBuffer("{\"report\":[");
	         ReportBean rb0=(ReportBean) map.get("汇总");
	         sb.append("{");
	    	  sb.append("\"单位名称\""+":\"汇总\",");
	    	  sb.append("\"案件总数\""+":\""+rb0.getAjzs()+"\",");
	    	  sb.append("\"已办按时办结\""+":\""+rb0.getYb_as()+"\",");
	    	  sb.append("\"已办超时办结\""+":\""+rb0.getYb_cs()+"\",");
	    	  sb.append("\"未办正在办理\""+":\""+rb0.getWb_as()+"\",");
	    	  sb.append("\"未办超时办理\""+":\""+rb0.getWb_cs()+"\"");
	    	  sb.append("},");
	         map.remove("汇总");
	       Set keys=  map.keySet();
	      
	       Iterator it1=keys.iterator();
	       while(it1.hasNext()){
	    	   sb.append("{");
	    	  String key= it1.next().toString();
	    	  sb.append("\"单位名称"+":\""+key+"\",");
	    	  ReportBean rb=(ReportBean)map.get(key);
	    	  sb.append("\"案件总数"+":\""+rb.getAjzs()+"\",");
	    	  sb.append("\"已办按时办结\""+":\""+rb.getYb_as()+"\",");
	    	  sb.append("\"已办超时办结\""+":\""+rb.getYb_cs()+"\",");
	    	  sb.append("\"未办正在办理\""+":\""+rb.getWb_as()+"\",");
	    	  sb.append("\"未办超时办理\""+":\""+rb.getWb_cs()+"\"");
	    	  sb.append("},");
	       }
	       sb=new StringBuffer(sb.substring(0, sb.length()-1));
	       sb.append("]}");
		return sb.toString();
	}
	
	public static void main(String []args){
		StringBuffer a=new StringBuffer("abc");
		System.out.println(a.substring(0, a.length()-1));
	}
    public Map getRunIdSetByQuery(Timestamp startTime,Timestamp endTime,String fieldName,Connection dbConn,String query){
    	PreparedStatement stmt = null;
        ResultSet rs = null;// 结果集
        Map map=new HashMap();
        map.put("汇总", new ReportBean());
        try {
          stmt = dbConn.prepareStatement(query);
          if (startTime != null) {
            stmt.setTimestamp(1, startTime);
            if (endTime != null) {
              stmt.setTimestamp(2, endTime);
            }
          } else {
            if (endTime != null) {
              stmt.setTimestamp(1, endTime);
            }
          }
          rs = stmt.executeQuery();
          while (rs.next()) {
        	  String dwmc=rs.getString(fieldName);
        	  if(dwmc==null || dwmc.equals("")){
        		  dwmc="未知";
        	  }
        	  Object o=map.get(dwmc);
        	 int runId=rs.getInt("run_id");
        	 String timeOutFlag=rs.getString("time_out_flag");
        	 
        	 Timestamp endtime=rs.getTimestamp("end_time");
        	  ReportBean rb=null;
        	  ReportBean rbhz=(ReportBean)map.get("汇总");
        	  if(o==null){
        		  rb =new ReportBean();
        		
        		  
        	  }
        	  else
        	  {
        		  rb=(ReportBean)o;
        	  }
        	  
        	  rb.setAjzs(rb.getAjzs()+1); 
        	  rbhz.setAjzs(rbhz.getAjzs()+1);
        	  if(endtime==null){
        		rb.setWb_runIdSet(rb.getWb_runIdSet()+runId+",");
        		if(timeOutFlag!=null && timeOutFlag.equals("1")){
        			rb.setWb_cs(rb.getWb_cs()+1);
        			rbhz.setWb_cs(rbhz.getWb_cs()+1);
        		}else{
        			rb.setWb_as(rb.getWb_as()+1);
        			rbhz.setWb_as(rbhz.getWb_as()+1);
        		}
        	  }
        	  else{
        		rb.setYb_runIdSet(rb.getYb_runIdSet()+runId+",");  
        		if(timeOutFlag!=null && timeOutFlag.equals("1")){
        			rb.setYb_cs(rb.getYb_cs()+1);
        			rbhz.setYb_cs(rbhz.getYb_cs()+1);
        		}else{
        			rb.setYb_as(rb.getYb_as()+1);
        			rbhz.setYb_as(rbhz.getYb_as()+1);
        		}
        	  }
        	  map.put(dwmc, rb);
          }
        } catch (Exception ex ) {
           ex.printStackTrace();
        } finally {
          YHDBUtility.close(stmt, rs, null);
        }
        return map;
    }
	public String getFieldByMgrKey(int formId,String mgrKey,Connection dbConn){
		   Statement stmt = null;
		   ResultSet rs = null;// 结果集
		   String title=map.get(mgrKey).toString();
		   String fieldName=null;
		   String query1="select NAME FROM OA_FL_FORM_ITEM WHERE FORM_ID="+formId+" AND TITLE='"+title+"'";
		 try {
		     stmt = dbConn.createStatement();
		     rs = stmt.executeQuery(query1);
		     if(rs.next()){
		    	 fieldName = rs.getString("NAME");
		     }
		     
		   }catch(Exception ex){
		     ex.printStackTrace();
		   }finally {
		     YHDBUtility.close(stmt, rs, null);
		   }
		   return fieldName;
	}
	public String getJsonByRequest(HttpServletRequest request){
		  Connection dbConn = null;
		  YHRequestDbConn requestDbConn = (YHRequestDbConn) request
          .getAttribute(YHBeanKeys.REQUEST_DB_CONN_MGR);
           try {
			dbConn = requestDbConn.getSysDbConn();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int flowTypeID=Integer.valueOf(request.getParameter("flowTypeID"));/*必填*/
		int flowPrcs=Integer.valueOf(request.getParameter("flowPrcs"));/*必填*/
		String beginDate=request.getParameter("beginDate");
		String endDate=request.getParameter("endDate");
		String dataTitle=request.getParameter("dataTitle");
		try {
			return this.getJsonByParameter(flowTypeID, flowPrcs, beginDate, endDate, dataTitle, dbConn);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
